package com.hongdee.crm.coupon.service.impl;

import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.approval.entity.TCrmCarApprovalFlowDetail;
import com.hongdee.crm.approval.service.ITCrmCarApprovalFlowDetailService;
import com.hongdee.crm.coupon.dao.ITCrmCarCouponDao;
import com.hongdee.crm.coupon.entity.TCrmCarCoupon;
import com.hongdee.crm.coupon.entity.TCrmCarCouponReissueApply;
import com.hongdee.crm.coupon.entity.TCrmCarCouponUseRecord;
import com.hongdee.crm.coupon.entity.TInsuranceOrderCoupon;
import com.hongdee.crm.coupon.mapper.ITCrmCarCouponMapper;
import com.hongdee.crm.coupon.service.ITCrmCarCouponService;
import jdk.nashorn.internal.parser.Token;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 名  称:TCrmCarCouponServiceImpl
 * 类  型:
 * 描  述:
 * 路  径:com.hongdee.crm.coupon.service.impl
 * 作  者: 术洪超
 * 日  期:2017-02-28 11:07
 */

@Service
public class TCrmCarCouponServiceImpl implements ITCrmCarCouponService {

    @Autowired
    ITCrmCarCouponMapper itCrmCarCouponMapper;
    private String tabName = "T_CRM_CAR_COUPON";

    //卡券业务审核角色
    private String serviceRoleId = "4028dd815da5edd1015da73a92760000";

    //卡券财务审核角色
    private String financeRoleId = "4028dd815da5edd1015da73b45710002";
    @Autowired
    private ITCrmCarCouponDao itCrmCarCouponDao;

    //审批明细
    @Autowired
    private ITCrmCarApprovalFlowDetailService itCrmCarApprovalFlowDetailService;

    /**
     * @param uid        登录用户 ID
     * @param start      起始记录
     * @param limit      页面大小
     * @param activityId 活动ID
     * @param status     优惠券状态
     * @param plateNum   车牌号
     * @param startDate  开始时间
     * @param endDate    结束时间
     * @return Map
     * @throws Exception
     * @desc 分页查询优惠券
     * @author 术洪超
     */
    @Override
    public Map page(String activityId, String status, String plateNum, Long startDate, Long endDate, String uid, String start,
                    String limit, String deptId, String createUser, String approvalStatus, String vin,
                    String nameOrTel, String companyId, String area, String brand) throws Exception {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = DateUtils.getTimeStr("yyyy-MM-dd 23:59:59");
        Date date1 = sdf.parse(date);
        Long nowDate = date1.getTime();

        //优惠券有效期小于当前时间的，自动更新状态为“3”  已过期
        String hql1 = "update TCrmCarCoupon set status = '3' where youxiaoqi <:nowDate and status = '1' ";
        Map params1 = new HashMap();
        params1.put("nowDate", nowDate);
        this.itCrmCarCouponDao.update(hql1, params1);

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList();
        this.pageToSql(false,sql, params, uid, activityId, status, startDate, endDate, deptId, plateNum, createUser, nameOrTel,
                approvalStatus, vin, companyId, area, brand);

        return this.itCrmCarCouponDao.page(sql.toString(), params.toArray(), start, limit);
    }

    @Override
    public Map pageForActivity(String activityId, String status, String plateNum, Long startDate, Long endDate, String uid, String start,
                    String limit, String deptId, String createUser, String approvalStatus, String vin,
                    String nameOrTel, String companyId, String area, String brand) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = DateUtils.getTimeStr("yyyy-MM-dd 23:59:59");
        Date date1 = sdf.parse(date);
        Long nowDate = date1.getTime();

        itCrmCarCouponMapper.UpdateStatus(nowDate.toString());

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList();
        this.pageToSql(true,sql, params, uid, activityId, status, startDate, endDate, deptId, plateNum, createUser, nameOrTel,
                approvalStatus, vin, companyId, area, brand);


        return this.itCrmCarCouponDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 优惠券管理查询生成sql
     *
     * @param sql
     * @param params
     * @param uid
     * @param activityId
     * @param status
     * @param startDate
     * @param endDate
     * @param companyId
     * @param plateNum
     * @param createUser
     * @param nameOrTel
     * @param approvalStatus
     * @param vin
     *
     *  +++ +++ +++ 新增加 isActivity 字段 ，true ：按照所有活动返回sql    false：按照优惠券返回sql
     */
    private void pageToSql(boolean isActivity,StringBuffer sql, List<Object> params, String uid, String activityId, String status, Long startDate,
                           Long endDate, String deptId, String plateNum, String createUser, String nameOrTel,
                           String approvalStatus, String vin, String companyId, String area, String brand) throws Exception {
        sql.append("select * ");
        if(isActivity){
            sql.append(",count(1) sum ");
            sql.append(",count(IF(STATUS = 0,1,NULL)) nPrint ");
            sql.append(",count(IF(STATUS = 1,1,NULL)) print ");
            sql.append(",count(IF(STATUS = 2,1,NULL)) used ");
        }
                sql.append( " from (select c.remainder, c.dayin_date ,c.id,a.name, a.gift,a.gscost,a.cost,a.degression, c.contact_name, c.use_date,c.use_money, c.youxiaoqi, " +
                " c.contact_phone, c.plate_num, c.num,  c.status,c.o_name," +
                " c.dept_name ,tco.order_num as gongdanId,c.create_time,tbc.u_name create_user ," +
                " case approval_status when '0' then '未提交' when '1' then '卡券业务审核中' when '2' then '卡券财务审核中' " +
                " when '3' then '审核通过' end shbz ,c.saler " +
                " from (select c.*,bc.o_name from t_crm_car_coupon c ");
        sql.append(",");
        sql.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" ) bc ");
        sql.append(" where bc.id=c.company_id )c");
        sql.append(" left join  t_crm_car_activity a on c.activity_id = a.id " +
                " left join t_bc_user tbc on tbc.id=c.create_user " +
                " left join t_crm_car_workorder tco on tco.order_num = c.gongdan_id and tco.company_id = c.company_id " +
                " AND tco.balance_date/1000 BETWEEN unix_timestamp(now())-604800 and unix_timestamp(now()) " +
                " and tco.balance_date in (select max(balance_date) from t_crm_car_workorder group by vin) " +
                " where c.status > '-1'  ");

        if (!StringUtils.isEmpty(activityId)) {
            sql.append(" and c.activity_id = ?");
            params.add(activityId);
        }
        //当条件中的状态和开始、结束时间同时不为空时，根据选择的状态变换不同的查询条件   状态为已使用时，判断使用时间；状态为已打印时（未使用），判断打印时间；状态为未打印时，判断创建时间; 状态为已过期时，判断有效期。
        if (!StringUtils.isEmpty(status)) {
            //已过期
            if ("3".equals(status)) {
                if (startDate != null && endDate == null) {
                    sql.append(" and c.status = ? and c.youxiaoqi >= ?");
                    params.add(status);
                    params.add(startDate);
                } else if (startDate != null && endDate != null) {
                    sql.append(" and c.status = ? and c.youxiaoqi >= ? and c.youxiaoqi< ?");
                    params.add(status);
                    params.add(startDate);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else if (startDate == null && endDate != null) {
                    sql.append(" and c.status = ? and c.youxiaoqi < ?");
                    params.add(status);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else {
                    sql.append(" and c.status = ?");
                    params.add(status);
                }
            }
            //已使用
            if ("2".equals(status)) {
                if (startDate != null && endDate == null) {
                    sql.append(" and c.status = ? and c.use_date >= ?");
                    params.add(status);
                    params.add(startDate);
                } else if (startDate != null && endDate != null) {
                    sql.append(" and c.status = ? and c.use_date >=? and c.use_date < ?");
                    params.add(status);
                    params.add(startDate);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else if (startDate == null && endDate != null) {
                    sql.append(" and c.status = ? and c.use_date < ?");
                    params.add(status);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else {
                    sql.append(" and c.status = ?");
                    params.add(status);
                }
            }
            //已打印
            if ("1".equals(status)) {
                if (startDate != null && endDate == null) {
                    sql.append(" and c.status = ? and c.dayin_date >= ?");
                    params.add(status);
                    params.add(startDate);
                } else if (startDate != null && endDate != null) {
                    sql.append(" and c.status = ? and c.dayin_date >=? and c.dayin_date < ?");
                    params.add(status);
                    params.add(startDate);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else if (startDate == null && endDate != null) {
                    sql.append(" and c.status = ? and c.dayin_date < ?");
                    params.add(status);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else {
                    sql.append(" and c.status = ?");
                    params.add(status);
                }
            }
            //未打印(新建)
            if ("0".equals(status)) {
                if (startDate != null && endDate == null) {
                    sql.append(" and c.status = ? and c.create_date >= ?");
                    params.add(status);
                    params.add(startDate);
                } else if (startDate != null && endDate != null) {
                    sql.append(" and c.status = ? and c.create_date >=? and c.create_date < ?");
                    params.add(status);
                    params.add(startDate);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else if (startDate == null && endDate != null) {
                    sql.append(" and c.status = ? and c.create_date < ?");
                    params.add(status);
                    params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
                } else {
                    sql.append(" and c.status = ?");
                    params.add(status);
                }
            }
        } else {
            if (startDate != null && endDate == null) {
                sql.append(" and c.create_date >= ?");
                params.add(startDate);
            }
            if (startDate != null && endDate != null) {
                sql.append(" and c.create_date >=? and c.create_date<?");
                params.add(startDate);
                params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
            }
            if (startDate == null && endDate != null) {
                sql.append(" and c.create_date < ?");
                params.add(DateUtils.addDayLongTime(Long.valueOf(endDate), +1));
            }
            sql.append(" and c.status != -1 ");
        }
        if (!StringUtils.isEmpty(plateNum)) {
            sql.append(" and c.plate_num like ?");
            params.add("%" + plateNum + "%");
        }
        //发起部门id
        if (!StringUtils.isEmpty(deptId)) {
            sql.append(" and c.dept_id = ?");
            params.add(deptId);
        }
        //创建人
        if (createUser != "" && !StringUtils.isEmpty(createUser)) {
            sql.append(" and c.create_user = ?");
            params.add(createUser);
        }
        //审核状态
        if (approvalStatus != "" && !StringUtils.isEmpty(approvalStatus)) {
            sql.append(" and c.approval_status = ? ");
            params.add(approvalStatus);
        }
        if (vin != "" && !StringUtils.isEmpty(vin)) {
            sql.append(" and c.vin like  ? ");
            params.add("%" + vin + "%");
        }
        if (nameOrTel != "" && !StringUtils.isEmpty(nameOrTel)) {
            sql.append(" and (c.contact_name like ? or contact_phone like ? ) ");
            params.add("%" + nameOrTel + "%");
            params.add("%" + nameOrTel + "%");
        }

        sql.append(" ORDER BY c.create_date desc ) as temp ");

        if(isActivity){
            sql.append(" GROUP BY NAME,plate_num ");
        }
    }

    /**
     * @param uid 登录用户 ID
     * @param id  主键 ID
     * @return Map
     * @throws Exception
     * @desc 查看详情
     * @author 术洪超
     */
    @Override
    public Map activityInfo(String id, String uid) throws Exception {

        String sql = "select a.*,o.o_address,o.o_name,c.city,o.o_phone,o.o_short_name from t_crm_car_activity a,t_bc_organ o,t_bc_cities c where o.id = a.company_id and o.o_city =c.cityid";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(id)) {
            throw new Exception("id不能为空！");
        }
        sql += " and a.id = ?";
        params.add(id);
        sql += " and a.company_id = ?";
        params.add(TokenUtil.getCompanyId(uid));
        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }

    /**
     * @param data 要保存的优惠券实体
     * @return
     * @throws Exception
     * @desc 保存添加或者修改的实体内容，通过主键判断
     * @author 术洪超
     */

    @Override
    public TCrmCarCoupon save(TCrmCarCoupon data, String aid, String uid) throws Exception {

        /****
         * 根据活动ID取得对应的活动信息
         *
         * **/
        //活动对应的发券数量
        Integer couCount = 1;
        //根据活动id回写活动发起部门
        Map temp = this.itCrmCarCouponDao.info("select tba.id,tba.o_name,ta.cou_count from  t_crm_car_activity ta" +
                " left join t_bc_organ tba on tba.id=ta.start_department where " +
                " ta.id = ? ", aid);

        // 处理优惠券信息
        data.setActivityId(aid);
        if (temp.size() > 0) {
            if (temp.get("id") != "" && !StringUtils.isEmpty(temp.get("id"))) {
                data.setDept_id(String.valueOf(temp.get("id")));
            }
            if (temp.get("oName") != "" && !StringUtils.isEmpty(temp.get("oName"))) {
                data.setDeptName(String.valueOf(temp.get("oName")));
            }
            //设置优惠券活动数量
            if (temp.get("couCount") != null && com.mysql.jdbc.StringUtils.isStrictlyNumeric(temp.get("couCount").toString())) {
                couCount = Integer.valueOf(temp.get("couCount").toString());
            }
        }

        //保存，审核状态为0未提交
        data.setApprovalStatus("0");
        data.setCaozuoName(uid);
        data.setStatus("0");
        data.setCreateUser(uid);
        data.setCreateTime(Calendar.getInstance().getTimeInMillis());
        data.setCreateDate(Calendar.getInstance().getTimeInMillis());
        //取得优惠券号
        String couponNum = data.getNum();
        //根据发券数量生成多张优惠券
        for (int i = 0; i < couCount; i++) {
            //优惠券分小号
            String tempNum = couponNum + "-" + (i + 1);
            data.setNum(tempNum);
            data.setId(null);
            this.itCrmCarCouponDao.saveOrUpdate(data);
        }
        return data;
    }

    /**
     * @param id     主键 ID
     * @param status 状态
     * @param uid    登录用户ID
     * @return
     * @throws Exception
     * @desc 修改状态并且写入操作人
     * @author 术洪超
     */

    @Override
    public void update(String id, String status, String uid) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择优惠券");
        }
        String hql = "update TCrmCarCoupon set status =:status, caozuoName =:uid, dayinName =:uid where id =:id";
        Map params = new HashMap();
        params.put("id", id);
        params.put("status", status);
        params.put("uid", uid);
        this.itCrmCarCouponDao.update(hql, params);

    }


    /**
     * @param uid    登录用户ID
     * @param id     主键 ID
     * @param status 状态
     * @return
     * @throws Exception
     * @desc 打印优惠券（修改状态为1）
     * @author 术洪超
     */
    @Override
    public void dayinUpdate(String id, String status, String uid, String youxiaoqi) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择优惠券");
        }

        String hql = "update TCrmCarCoupon set youxiaoqi =:youxiaoqi, status =:status, dayinName =:uid, dayinDate =:dayinDate, caozuoName =:uid where id =:id";
        Map params = new HashMap();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = simpleDateFormat.parse(youxiaoqi);
        params.put("id", id);
        params.put("status", status);
        params.put("dayinDate", System.currentTimeMillis());
        params.put("uid", uid);
        params.put("youxiaoqi", date.getTime());
        this.itCrmCarCouponDao.update(hql, params);

    }


    //优惠券使用

    /**
     * @param uid        登录用户 ID
     * @param num        优惠券号
     * @param plateNum   车牌号
     * @param activityId 活动ID
     * @return Map
     * @throws Exception
     * @desc 查询优惠券详情，显示信息供销券
     * @author 术洪超
     */

    @Override
    public Map useInfo(String num, String plateNum, String activityId, String uid) throws Exception {

        String sql = "select c.*, u.u_code caozuoCode, u2.u_code dayinCode, u3.u_code useCode, a.name, a.gift, a.degression, a.cost, a.gscost from " + tabName + " c left join t_crm_car_activity a on c.activity_id = a.id left join t_bc_user u on u.id = c.create_user left join t_bc_user u2 on u2.id = c.dayin_name left join t_bc_user u3 on  u3.id = c.caozuo_name where 1=1 ";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(activityId)) {

            if (StringUtils.isEmpty(num) && StringUtils.isEmpty(plateNum)) {
                throw new Exception("请输入查询内容，活动与券号或者车牌号配合使用！");
            }

            if (!StringUtils.isEmpty(num)) {
                sql += " and c.num = ?";
                params.add(num);
            }

            if (!StringUtils.isEmpty(plateNum)) {
                sql += " and c.plate_num like ?";
                params.add("%" + plateNum + "%");
            }
        }

        if (!StringUtils.isEmpty(activityId)) {

            if (StringUtils.isEmpty(num) && StringUtils.isEmpty(plateNum)) {
                throw new Exception("请输入查询内容，活动与券号或者车牌号配合使用！");
            }

            if (!StringUtils.isEmpty(plateNum) && StringUtils.isEmpty(num)) {
                sql += " and c.plate_num like ? ";
                params.add("%" + plateNum + "%");
                sql += " and c.activity_id = ?";
                params.add(activityId);
            }
            if (StringUtils.isEmpty(plateNum) && !StringUtils.isEmpty(num)) {
                sql += " and c.num = ?";
                params.add(num);
                sql += " and c.activity_id = ?";
                params.add(activityId);
            }
        }


        String oId = TokenUtil.getCompanyId(uid);
        sql += " and c.company_id = ? AND c.approval_status = '3' AND c.status = '1' limit 1";
        params.add(oId);

        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }

    /**
     * @param num       优惠券号
     * @param status    要修改的状态，使用时修改状态为2
     * @param useRemark 使用说明，PC销券或者手机销券
     * @param uid       登录用户ID
     * @param gongdanId 工单号
     * @param status    状态
     * @param remainder 余额
     * @param useMoney  使用金额
     * @return
     * @throws Exception
     * @desc 使用优惠券，修改状态2，同时添加使用记录。
     * @author 术洪超
     */
    @Override
    public void use(String num, String status, String useRemark, String remainder, String useMoney, String uid, String gongdanId) throws Exception {

        if (StringUtils.isEmpty(num)) {
            throw new Exception("请选择优惠券");
        }

        List param = new ArrayList();
        String sql = "select youxiaoqi from t_crm_car_coupon where num = ?";
        param.add(num);
        Map map = this.itCrmCarCouponDao.info(sql, param.toArray());
        Long youxiaoqi = Long.valueOf(String.valueOf(map.get("youxiaoqi")));

        String hql = "update TCrmCarCoupon set use_money=:useMoney, remainder=:remainder, status =:status, caozuo_name =:uid, use_remark =:useRemark, gongdan_id =:gongdanId, use_date =:useDate where num =:num";

        if (youxiaoqi < System.currentTimeMillis()) {

            throw new Exception("很遗憾，优惠券已过期！");

        } else {
            Map params = new HashMap();
            params.put("useMoney", useMoney);
            params.put("remainder", remainder);
            params.put("status", status);
            params.put("uid", uid);
            params.put("useRemark", useRemark);
            params.put("gongdanId", gongdanId);
            params.put("num", num);
            params.put("useDate", Calendar.getInstance().getTimeInMillis());

            this.itCrmCarCouponDao.update(hql, params);
        }


        if (StringUtils.isEmpty(num)) {
            throw new Exception("请输入优惠券号！");
        } else {
            String sql1 = "select * from t_crm_car_coupon where num = ? and company_id = ?";
            List<Object> params1 = new ArrayList<>();
            params1.add(num);
            params1.add(TokenUtil.getCompanyId(uid));

            Map map1 = this.itCrmCarCouponDao.info(sql1, params1.toArray());


            String sql2 = "select a.*,c.use_money,c.coupon_type,c.remainder,c.activity_id,c.youxiaoqi,c.plate_num,c.contact_name,c.vin,c.id as couponId from  t_crm_car_activity a left join t_crm_car_coupon c on a.id = c.activity_id where c.num = ? and c.company_id = ?";
            List<Object> params2 = new ArrayList<>();
            params2.add(num);
            params2.add(TokenUtil.getCompanyId(uid));

            Map map2 = this.itCrmCarCouponDao.info(sql2, params2.toArray());

            TCrmCarCouponUseRecord useRecord = new TCrmCarCouponUseRecord();
            useRecord.setActivityName(String.valueOf(map2.get("name")));
            useRecord.setGift(String.valueOf(map2.get("gift")));
            useRecord.setDegression(String.valueOf(map2.get("degression")));
            useRecord.setCost(String.valueOf(map2.get("cost")));
            useRecord.setGscost(String.valueOf(map2.get("gscost")));
            useRecord.setActivityId(String.valueOf(map2.get("activityId")));

            useRecord.setContactName(String.valueOf(map1.get("contactName")));
            useRecord.setContactPhone(String.valueOf(map1.get("contactPhone")));
            useRecord.setPlateNum(String.valueOf(map1.get("plateNum")));
            useRecord.setNum(String.valueOf(map1.get("num")));
            useRecord.setGongdanId(String.valueOf(map1.get("gongdanId")));
            useRecord.setUseRemark(String.valueOf(map1.get("useRemark")));
            useRecord.setPrintDate(Long.valueOf(String.valueOf(map1.get("dayinDate"))));
            useRecord.setUseDate(Long.parseLong(String.valueOf(map1.get("useDate"))));
            useRecord.setCreateTime(Calendar.getInstance().getTimeInMillis());
            useRecord.setCreateUser(uid);
            useRecord.setCompanyId(TokenUtil.getCompanyId(uid));
            useRecord.setCouponType(String.valueOf(map2.get("couponType")));
            useRecord.setRemainder(String.valueOf(map2.get("remainder")));
            useRecord.setUseMoney(String.valueOf(map2.get("useMoney")));


            this.itCrmCarCouponDao.saveOrUpdate(useRecord);
//            //更新其优惠券相对应的任务
//            String tpl="%s 的车主[%s]参加活动[%s]的优惠券将于%s到期";
//            String vin= StringUtil.formatNull(map2.get("vin"));
//            String plateNum= StringUtil.formatNull(map2.get("plateNum"));
//            Long  youxiaoqiL=Long.valueOf(StringUtil.formatNull(map2.get("youxiaoqi")));
//            String youxiaoqiStr=DateUtils.formatLongTime(youxiaoqiL,"yyyy-MM-dd");
//            String contactName=StringUtil.formatNull(map2.get("contactName"));
//            String content=String.format(tpl,plateNum,contactName,useRecord.getActivityName(),youxiaoqiStr);
            if (status == "2") {
                String sqlcoupon = "update t_crm_task_real set status=2 ,is_back=1 where contact_id =? and company_id=?";
                this.itCrmCarCouponDao.executeSQL(sqlcoupon, String.valueOf(map2.get("couponId")), TokenUtil.getCompany(uid));
            }

        }
    }


    /**
     * @param plateNum 车牌号
     * @return Map
     * @throws Exception
     * @desc 查询车辆详情，填写优惠券信息
     * @author 术洪超
     */
    @Override
    public Map carCouponInfo(String plateNum, String uid) throws Exception {
        String sql = "select vin,owner_name,owner_tel from t_crm_car where 1=1";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(plateNum)) {
            throw new Exception("请输入车牌号查询！");
        } else {
            sql += " and plate_num = ?";
            params.add(plateNum);
        }
        sql += " and company_id = ?  limit 1";
        params.add(TokenUtil.getCompanyId(uid));

        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }


    /**
     * @param uid        登录用户
     * @param start      起始记录
     * @param limit      页面大小
     * @param activityId 活动ID
     * @param plateNum   车牌号
     * @param startDate  查询起始时间
     * @param endDate    查询结束时间
     * @return Map
     * @throws Exception
     * @desc 优惠券使用记录分页查询
     * @author 术洪超
     */
    @Override
    public Map useRecordPage(String activityId, String plateNum, String startDate, String endDate, String uid, String start,
                             String limit, String companyId, String area, String brand) throws Exception {

        StringBuffer sql = new StringBuffer("select c.*,o.o_name from t_crm_car_coupon_userecord c");
        List<Object> params = new ArrayList<>();
        sql.append(" left join t_bc_organ o ");
        sql.append(" on c.company_id=o.id ");
        sql.append(" where c.company_id = ? ");
        params.add(companyId);
        // Constant.companyAndAreaAndBrand(uid,companyId,area,brand,sql,params,"o");


        if (!StringUtils.isEmpty(activityId)) {
            sql.append(" and c.activity_id = ?");
            params.add(activityId);
        }
        if (!StringUtils.isEmpty(startDate) && (StringUtils.isEmpty(endDate))) {
            sql.append(" and c.use_date >= ?");
            params.add(DateUtils.parseTime(startDate, DateUtils.YYYYMMDD));
        } else if (!StringUtils.isEmpty(startDate) && (!StringUtils.isEmpty(endDate))) {
            sql.append(" and c.use_date >=?  and c.use_date<?");
            params.add(DateUtils.parseTime(startDate, DateUtils.YYYYMMDD));
            Long endDateL = DateUtils.parseTime(endDate, DateUtils.YYYYMMDD);
            params.add(DateUtils.addDayLongTime(endDateL, +1));
        } else if (StringUtils.isEmpty(startDate) && (!StringUtils.isEmpty(endDate))) {
            sql.append(" and c.use_date < ?");
            Long endDateL = DateUtils.parseTime(endDate, DateUtils.YYYYMMDD);
            params.add(DateUtils.addDayLongTime(endDateL, +1));
        }

        if (!StringUtils.isEmpty(plateNum)) {
            sql.append(" and c.plate_num like ?");
            params.add("%" + plateNum + "%");
        }

        sql.append(" order by c.create_time desc ");
        return this.itCrmCarCouponDao.page(sql.toString(), params.toArray(), start, limit);
    }


    //优惠券补发

    /**
     * @return
     * @throws Exception
     * @desc 要补发的优惠券下拉列表
     * @author 术洪超
     */
    @Override
    public List<Map> couponReissueCombo(String plateNum, String uid, String vin) throws Exception {
        List params = new ArrayList();
        String sql = " select a.id, a.name,c.num from t_crm_car_activity a left join t_crm_car_coupon c on a.id = c.activity_id where c.status = '1' ";
        if (!StringUtils.isEmpty(plateNum)) {
            sql += " and c.plate_num = ? ";
            params.add(plateNum);
        }
        if (!StringUtils.isEmpty(vin)) {
            sql += " and c.vin = ? ";
            params.add(vin);
        }
      /* if(!StringUtils.isEmpty(plateNum)){

       }*/
       /* if(!StringUtils.isEmpty(vin)){
            sql += " and c.vin like ?";
            params.add("%" + vin + "%");
        }*/


        sql += " and c.company_id = ? order by c.create_time desc ";
        params.add(TokenUtil.getCompanyId(uid));
        List<Map<String, Object>> mapList = this.itCrmCarCouponDao.list(sql, params.toArray());

        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("value", map.get("num"));
            temp.put("label", map.get("name") + "-" + map.get("num"));
            resList.add(temp);
        }
        return resList;
    }

    /**
     * @param uid      登录用户ID
     * @param plateNum 车牌号
     * @return ResultEntity
     * @throws Exception
     * @desc 查询补发时优惠券的详情
     * @author 术洪超
     */
    @Override
    public Map reissueInfo(String plateNum, String uid, String vin) throws Exception {

        String sql = "select * from t_crm_car_coupon where 1=1 and status = '1'";
        List<Object> params = new ArrayList<>();
      /*  if (StringUtils.isEmpty(plateNum)) {
            throw new Exception("请输入车牌号！");
        }*/
        if (!StringUtils.isEmpty(plateNum)) {
            sql += " and plate_num = ?";
            params.add(plateNum);
        }
        if (!StringUtils.isEmpty(vin)) {
            sql += " and vin = ?";
            params.add(vin);
        }


        sql += " and company_id = ? limit 1";
        params.add(TokenUtil.getCompanyId(uid));
        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }

    /**
     * @param data 要保存的变更的补发的优惠券实体
     * @return
     * @throws Exception
     * @desc 保存添加或者修改的实体内容
     * @author 术洪超
     */
    @Override
    public TCrmCarCouponReissueApply save(TCrmCarCouponReissueApply data, String uid) throws Exception {
        data.setApplyUid(uid);
        data.setStatus("0");
        data.setCreateUser(uid);
        data.setCreateTime(Calendar.getInstance().getTimeInMillis());
        data.setApplyDate(Calendar.getInstance().getTimeInMillis());
        this.itCrmCarCouponDao.saveOrUpdate(data);
        return data;
    }

    /**
     * @param uid      登录用户
     * @param start    起始记录
     * @param limit    页面大小
     * @param applyUid 申请用户
     * @return Map
     * @throws Exception
     * @desc 申请补发优惠券列表
     * @author 术洪超
     */
    @Override

    public Map applyPage(String applyUid, String uid, String start, String limit) throws Exception {

        String companyId = TokenUtil.getCompanyId(uid);
        List<Object> params = new ArrayList();
        String sql = "SELECT r.*, u.u_code,u.u_name, c.contact_name, c.contact_phone, a.NAME, a.gift, c.plate_num FROM t_crm_car_coupon_reissue_apply r LEFT JOIN t_crm_car_coupon c ON c.num = r.num LEFT JOIN t_crm_car_activity a ON a.id = c.activity_id LEFT JOIN t_bc_user u ON u.id = r.apply_uid where r.company_id = ? ";
        params.add(companyId);

        /**
         * 申请人
         * **/
        if (!StringUtils.isEmpty(applyUid)) {
            sql += " and (u.u_code like ? or u.u_name like ? )";
            params.add("%" + applyUid + "%");
            params.add("%" + applyUid + "%");

        }

        sql += " order by r.create_time desc";

        return this.itCrmCarCouponDao.page(sql, params.toArray(), start, limit);
    }

    /**
     * @param num    主键 ID
     * @param status 状态
     * @return
     * @throws Exception
     * @desc 处理补发申请，同意补发修改状态为1，拒绝补发修改状态为2
     * @author 术洪超
     */

    @Override
    public void applyUpdate(String id, String num, String status, String uid) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择要补发的优惠券");
        }

        List apStatus = new ArrayList();
        String sql = "select status from t_crm_car_coupon where num = ?";
        apStatus.add(num);
        Map map = this.itCrmCarCouponDao.info(sql, apStatus.toArray());

        if (map.get("status").equals("2")) {

            //如果优惠券已使用，那么补发申请直接改为拒绝，然后抛异常
            String hql = "update TCrmCarCouponReissueApply set status = '2' where id =:id and companyId =:uid";
            Map params = new HashMap();
            params.put("id", id);
            params.put("uid", TokenUtil.getCompanyId(uid));
            this.itCrmCarCouponDao.update(hql, params);

            throw new Exception("优惠券已使用！");

        } else {
            String hql = "update TCrmCarCouponReissueApply set status =:status where id =:id and companyId =:uid";
            Map params = new HashMap();
            params.put("id", id);
            params.put("status", status);
            params.put("uid", TokenUtil.getCompanyId(uid));
            this.itCrmCarCouponDao.update(hql, params);

            String hql2 = "update TCrmCarCoupon set status =:status where num =:num and companyId =:uid";

            Map params2 = new HashMap();
            params2.put("num", num);
            params2.put("status", "0");
            params2.put("uid", TokenUtil.getCompanyId(uid));
            this.itCrmCarCouponDao.update(hql2, params2);

        }

    }

    /**
     * @param uid 登录用户ID
     * @param id  申请补发优惠券主键
     * @return Map
     * @throws Exception
     * @desc 查询申请补发的优惠券的详情
     * @author 术洪超
     */

    @Override
    public Map applyInfo(String id, String uid) throws Exception {

        String sql = "select u1.u_code createCode, u2.u_code dayinCode, c.*,c.status cStatus ,a.*,r.* from t_crm_car_coupon_reissue_apply r left join t_crm_car_coupon c on r.num = c.num LEFT JOIN  t_crm_car_activity a on a.id = c.activity_id left join t_bc_user u1 on u1.id = r.create_user left join t_bc_user u2 on u2.id = c.dayin_name where 1=1 ";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(id)) {
            throw new Exception("id不能为空！");
        }
        sql += " and r.id = ?";
        params.add(id);

        sql += " and c.company_id = ?";
        params.add(TokenUtil.getCompanyId(uid));
        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }

    /**
     * @param uid      登录用户
     * @param start    起始记录
     * @param limit    页面大小
     * @param plateNum 车牌号
     * @param num      优惠券号
     * @return Map
     * @throws Exception
     * @desc 已同意补发的分页查询
     * @author 术洪超
     */
    @Override
    public Map reissuePage(String plateNum, String num, String uid, String start, String limit) throws Exception {

        String sql = "select a.name, a.gift, c.contact_name, c.contact_phone, r.*,c.plate_num from t_crm_car_activity a, t_crm_car_coupon c , t_crm_car_coupon_reissue_apply r where 1=1 and r.num = c.num and a.id = c.activity_id and r.status = '1' ";
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(plateNum)) {
            sql += " and c.plate_num like ?";
            params.add("%" + plateNum + "%");
        }
        if (!StringUtils.isEmpty(num)) {
            sql += " and r.num like ?";
            params.add("%" + num + "%");
        }

        sql += " and r.company_id = ? ";
        params.add(TokenUtil.getCompanyId(uid));

        sql += "order by r.create_time desc";

        return this.itCrmCarCouponDao.page(sql, params.toArray(), start, limit);
    }

    /**
     * @param uid      登录用户
     * @param start    起始记录
     * @param limit    页面大小
     * @param plateNum 车牌号
     * @param num      优惠券号
     * @return Map
     * @throws Exception
     * @desc 待激活优惠券分页查询(已过期的优惠券可激活)
     * @author 术洪超
     */
    @Override
    public Map actionPage(String plateNum, String num, String uid, String start, String limit) throws Exception {
        String sql = "select c.*,a.name,a.gift from t_crm_car_activity a left join t_crm_car_coupon c on a.id = c.activity_id where c.youxiaoqi < (unix_timestamp(now()) * 1000) and c.status = '3' ";
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(plateNum)) {
            sql += " and c.plate_num like ?";
            params.add("%" + plateNum + "%");
        }
        if (!StringUtils.isEmpty(num)) {
            sql += " and c.num like ?";
            params.add("%" + num + "%");
        }

        sql += " and c.company_id = ? order by c.create_time desc";
        params.add(TokenUtil.getCompanyId(uid));
        return this.itCrmCarCouponDao.page(sql, params.toArray(), start, limit);
    }


    /**
     * @param id 需要激活的优惠券的主键
     * @return Map
     * @throws Exception
     * @desc 查询需要激活的优惠券的详情
     * @author 术洪超
     */
    @Override
    public Map actionInfo(String id, String uid) throws Exception {

        String sql = "select u1.u_code createCode, u2.u_code dayinCode, c.*, a.* from t_crm_car_coupon c left join t_crm_car_activity a on a.id = c.activity_id left join t_bc_user u1 on u1.id = c.create_user left join t_bc_user u2 on u2.id = c.dayin_name where 1=1";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(id)) {
            throw new Exception("id不能为空！");
        }
        sql += " and c.id = ?";
        params.add(id);

        sql += " and c.company_id = ?";
        params.add(TokenUtil.getCompanyId(uid));
        return this.itCrmCarCouponDao.info(sql, params.toArray());
    }

    /**
     * @param id  主键 ID
     * @param uid 登录用户ID
     * @return
     * @throws Exception
     * @desc 修改状态
     * @author 术洪超
     */
    @Override
    public void actionUpdate(String id, String uid) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择优惠券");
        }

        Calendar now = Calendar.getInstance();

        now.add(Calendar.DATE, 1);

        Long longDate = now.getTimeInMillis();

        String hql = "update TCrmCarCoupon set youxiaoqi =:youxiaoqi, caozuoName =:uid,status =:status where id =:id";
        Map params = new HashMap();
        params.put("id", id);
        params.put("youxiaoqi", longDate);
        params.put("uid", uid);
        params.put("status", "1");
        this.itCrmCarCouponDao.update(hql, params);

    }

    /**
     * @param id 主键 ID
     * @return Map
     * @throws Exception
     * @desc 查询优惠券详情
     * @author 术洪超
     */
    //供打印时查询详情
    @Override
    public Map couponInfo(String id, String uid, String aid) throws Exception {


        String sql = "select c.*,a.name,a.gift,a.cost,a.qixian,a.message,o.o_address,o.o_name,ct.city,o.o_short_name,o.o_phone from t_crm_car_coupon c,t_bc_organ o,t_bc_cities ct,t_crm_car_activity a where a.id = c.activity_id and o.id = c.company_id and o.o_city = ct.cityid ";

        List params = new ArrayList();

        if (StringUtils.isEmpty(id)) {
            throw new Exception("id不能为空！");
        }
        sql += " and c.id = ?";
        params.add(id);

        sql += " and c.company_id = ?";
        params.add(TokenUtil.getCompanyId(uid));

        Map couInfo = this.itCrmCarCouponDao.info(sql, params.toArray());


        //计算有效期  优惠券打印时间加上活动的期限等于有效日期
        Calendar calendar = Calendar.getInstance();

        calendar.setTime(new Date());
        calendar.add(Calendar.MONTH, Integer.valueOf(String.valueOf(couInfo.get("qixian"))));

        couInfo.put("youxiaoqi", calendar.getTime().getTime());
        return couInfo;

    }


    // 优惠券导出字段
    String[] excelHeaderCouponA = {"优惠券号", "所属活动", "礼品名称", "有效期", "车牌号", "车主姓名", "联系电话", "打印时间", "使用时间", "关联工单号", "销售人员", "剩余金额", "登记人员", "成本","礼品价值","优惠券状态"};
    // 活动导出字段
     String[] excelHeaderActivityB = {"已使用数/优惠券数","活动名称", "礼品名称", "有效期", "车牌号", "车主姓名", "联系电话","已打印数","未打印数", "关联工单号", "销售人员", "剩余金额", "登记人员", "成本","礼品价值"}; //, "已使用数"


    @Override
    public XSSFWorkbook exportCoupon(boolean isActivity,List list, String uid) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet;
        if(isActivity){
            sheet = wb.createSheet("活动管理");
        }
        else {
            sheet = wb.createSheet("优惠券管理");
        }
        String[] excelHeaderCoupon;

        if(isActivity){
            excelHeaderCoupon = excelHeaderActivityB;
        }
        else {
            excelHeaderCoupon = excelHeaderCouponA;
        }

        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderCoupon.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderCoupon[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }
        Map temp;
        String tempStr;
        String tempStr2;
        for (int i = 0; i < list.size(); i++) {
            temp = (Map) list.get(i);

            row = sheet.createRow(i + 1);

            if(isActivity){
                tempStr = temp.get("sum") == null ? "0" : temp.get("sum").toString();
                tempStr2 = temp.get("used") == null ? "0" : temp.get("used").toString();
                row.createCell(0).setCellValue(tempStr2+"/"+tempStr);
            }
            else {
                tempStr = temp.get("num") == null ? "" : temp.get("num").toString();
                row.createCell(0).setCellValue(tempStr);
            }




            tempStr = temp.get("name") == null ? "" : temp.get("name").toString();
            row.createCell(1).setCellValue(tempStr);


            tempStr = temp.get("gift") == null ? "" : temp.get("gift").toString();
            row.createCell(2).setCellValue(tempStr);

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

            if (temp.get("youxiaoqi") != null) {
                Long youxiaoqi = Long.valueOf(temp.get("youxiaoqi").toString());
                String yxq = simpleDateFormat.format(youxiaoqi);
                row.createCell(3).setCellValue(yxq);
            } else {
                row.createCell(3).setCellValue("");
            }
            tempStr = temp.get("plateNum") == null ? "" : temp.get("plateNum").toString();
            row.createCell(4).setCellValue(tempStr);
            tempStr = temp.get("contactName") == null ? "" : temp.get("contactName").toString();
            row.createCell(5).setCellValue(tempStr);
            tempStr = temp.get("contactPhone") == null ? "" : temp.get("contactPhone").toString();
            row.createCell(6).setCellValue(tempStr);

            SimpleDateFormat simpleDateFormats = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            if(isActivity){
                tempStr = temp.get("print") == null ? "0" : temp.get("print").toString();
                row.createCell(7).setCellValue(tempStr);

                tempStr = temp.get("nPrint") == null ? "0" : temp.get("nPrint").toString();
                row.createCell(8).setCellValue(tempStr);

               /* tempStr = temp.get("used") == null ? "0" : temp.get("used").toString();
                row.createCell(18).setCellValue(tempStr);*/
            }else {
                if (temp.get("dayinDate") != null) {
                    Long dayinDate = Long.valueOf(temp.get("dayinDate").toString());
                    String dDate = simpleDateFormats.format(dayinDate);
                    row.createCell(7).setCellValue(dDate);

                } else {
                    row.createCell(7).setCellValue("");
                }


                if (temp.get("useDate") != null) {

                    Long useDate = Long.valueOf(temp.get("useDate").toString());
                    String uDate = simpleDateFormats.format(useDate);
                    row.createCell(8).setCellValue(uDate);

                } else {
                    row.createCell(8).setCellValue("");
                }
            }




            tempStr = temp.get("gongdanId") == null ? "" : temp.get("gongdanId").toString();
            row.createCell(9).setCellValue(tempStr);



            tempStr = temp.get("saler") == null ? "" : temp.get("saler").toString();
            row.createCell(10).setCellValue(tempStr);
            tempStr = temp.get("remainder") == null ? "" : temp.get("remainder").toString();
            row.createCell(11).setCellValue(tempStr);
            tempStr = temp.get("createUser") == null ? "" : temp.get("createUser").toString();
            row.createCell(12).setCellValue(tempStr);

            /**新增成本字段 2017、09、29**/
            String gcost = temp.get("gscost") == null ? "0" : temp.get("gscost").toString();
            String des = temp.get("degression") == null ? "0" : temp.get("degression").toString();



            try {
                tempStr = String.valueOf(new BigDecimal(gcost).add(new BigDecimal(des)).setScale(2, BigDecimal.ROUND_HALF_EVEN).doubleValue());

            } catch (Exception e) {
                tempStr = "0";
            }
            row.createCell(13).setCellValue(tempStr);

            tempStr = temp.get("cost") == null ? "0" : temp.get("cost").toString();
            row.createCell(14).setCellValue(tempStr);

            if(!isActivity){
                tempStr = temp.get("status") == null ? "" : temp.get("status").toString();
                switch (tempStr) {
                    case "-1":
                        tempStr = "已删除";
                        break;
                    case "0":
                        tempStr = "未打印";
                        break;
                    case "1":
                        tempStr = "已打印";
                        break;
                    case "2":
                        tempStr = "已使用";
                        break;
                    default:
                        tempStr = "";
                        break;
                }
                row.createCell(15).setCellValue(tempStr);
            }





        }
        return wb;
    }

    /***
     * 查询优惠券记录并导出-
     * @param activityId
     * @param status
     * @param plateNum
     * @param startDate
     * @param endDate
     * @param uid
     * @param deptId
     * @param createUser
     * @param approvalStatus
     * @param vin
     * @param nameOrTel
     * @param companyId
     * @param area
     * @param brand
     * @return
     * @throws Exception
     * @author 张国凯
     */
    @Override
    public List findListCoupon(String activityId, String status, String plateNum, Long startDate, Long endDate,
                               String uid, String deptId, String createUser, String approvalStatus, String vin,
                               String nameOrTel, String companyId, String area, String brand) throws Exception {

        Map page = this.page(activityId, status, plateNum, startDate, endDate, uid, "0", "1655360", deptId, createUser,
                approvalStatus, vin, nameOrTel, companyId, area, brand);
        return (List) page.get("dataList");
    }

    @Override
    public List findListActivity(String activityId, String status, String plateNum, Long startDate, Long endDate,
                               String uid, String deptId, String createUser, String approvalStatus, String vin,
                               String nameOrTel, String companyId, String area, String brand) throws Exception {

        Map page = this.pageForActivity(activityId, status, plateNum, startDate, endDate, uid, "0", "1655360", deptId, createUser,
                approvalStatus, vin, nameOrTel, companyId, area, brand);
        return (List) page.get("dataList");
    }


    String[] excelHeaderRecord = {"优惠券号", "使用工单", "使用时间", "使用备注", "发券时间", "所属活动", "赠送礼品", "活动成本", "礼品价值", "车牌号", "车主姓名", "联系电话", "剩余金额", "使用金额"};


    /***
     * 活动详情导出
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public List findListCoupono(String activity_id, String uid) throws Exception {
        List params = new ArrayList();
        String sql = "select a.id, a.name, a. status,a.gift, a.start_date, a.stop_date, a.cost, a.gscost, c.dayin_date,c.id as coid, c.num,c.gongdan_id,c.plate_num,c.contact_name,c.contact_phone, c.activity_id, c.use_date,c. status as cstatus, c.company_id from t_crm_car_coupon c LEFT JOIN t_crm_car_activity a ON c.activity_id = a.id  WHERE c.company_id =? and c.activity_id= ? and a.name= ?  ";
        params.add(TokenUtil.getCompanyId(uid));
        params.add(activity_id);
        List<Map<String, Object>> list1 = this.itCrmCarCouponDao.list(sql, params.toArray());
        List tCrmCarCouponList = new ArrayList<>();
        for (Map map : list1) {
            TCrmCarCoupon tCrmCarCoupon = new TCrmCarCoupon();
            tCrmCarCoupon.setActivityId(String.valueOf(map.get("name")));
            tCrmCarCoupon.setContactName(String.valueOf(map.get("contactName")));
            System.out.println(map.get("contactName"));

            if (map.get("useDate") != null) {
                tCrmCarCoupon.setUseDate(Long.valueOf(String.valueOf(map.get("useDate"))));
            } else {
                tCrmCarCoupon.setUseDate(null);
            }
            if (map.get("dayinDate") != null) {
                tCrmCarCoupon.setDayinDate(Long.valueOf(String.valueOf(map.get("dayinDate"))));
            } else {
                tCrmCarCoupon.setDayinDate(null);
            }
            System.out.println(map.get("dayinDate") + "dayinshijian===============");

            tCrmCarCoupon.setContactPhone(String.valueOf(map.get("contactPhone")));
            tCrmCarCoupon.setPlateNum(String.valueOf(map.get("plateNum")));
            tCrmCarCoupon.setNum(String.valueOf(map.get("num")));
            if (map.get("gongdanId") != null) {
                tCrmCarCoupon.setGongdanId(String.valueOf(map.get("gongdanId")));
            } else {
                tCrmCarCoupon.setGongdanId(null);
            }

            if (map.get("startDate") != null) {
                tCrmCarCoupon.setCreateTime(Long.valueOf(String.valueOf(map.get("startDate"))));
            } else {
                tCrmCarCoupon.setCreateTime(null);
            }
            if (map.get("stopDate") != null) {
                tCrmCarCoupon.setUpdateTime(Long.valueOf(String.valueOf(map.get("stopDate"))));
            } else {
                tCrmCarCoupon.setUpdateTime(null);
            }
            tCrmCarCoupon.setUseRemark(String.valueOf(map.get("gift")));
            tCrmCarCoupon.setComboId(String.valueOf(map.get("gscost")));
            tCrmCarCoupon.setRemart(String.valueOf(map.get("cost")));


            tCrmCarCouponList.add(tCrmCarCoupon);

        }
        return tCrmCarCouponList;
    }


    String[] excelHeaderRecordy = {"优惠券号", "打印时间", "使用时间", "使用工单", "车牌号", "车主姓名", "联系电话", "所属活动", "赠送礼品", "活动开始", "活动结束", "活动成本"};


    @Override
    public HSSFWorkbook exportRecordy(List<TCrmCarCoupon> list, String uid) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("优惠券使用");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecord.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordy[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TCrmCarCoupon tCrmCarCoupon = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tCrmCarCoupon.getNum());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");
            Long dayinDate = tCrmCarCoupon.getDayinDate();
            String dDate = "";
            if (dayinDate != null) {
                dDate = simpleDateFormat.format(dayinDate);
            }
            row.createCell(1).setCellValue(dDate);
            ;
            Long useDate = tCrmCarCoupon.getUseDate();
            String uDate = "";
            if (useDate != null) {
                uDate = simpleDateFormat.format(useDate);
            }
            ;
            row.createCell(2).setCellValue(uDate);
            row.createCell(3).setCellValue(tCrmCarCoupon.getGongdanId());
            row.createCell(4).setCellValue(tCrmCarCoupon.getPlateNum());
            row.createCell(5).setCellValue(tCrmCarCoupon.getContactName());
            row.createCell(6).setCellValue(tCrmCarCoupon.getContactPhone());
            row.createCell(7).setCellValue(tCrmCarCoupon.getActivityId());
            row.createCell(8).setCellValue(tCrmCarCoupon.getUseRemark());

            Long startDate = tCrmCarCoupon.getCreateTime();
            String gDate = "";
            if (startDate != null) {
                gDate = simpleDateFormat.format(startDate);
            }
            row.createCell(9).setCellValue(gDate);
            Long stopDate = tCrmCarCoupon.getUpdateTime();
            String sDate = "";
            if (stopDate != null) {
                sDate = simpleDateFormat.format(stopDate);
            }
            row.createCell(10).setCellValue(sDate);
            row.createCell(11).setCellValue(Integer.valueOf(tCrmCarCoupon.getComboId()) + Integer.valueOf(tCrmCarCoupon.getRemart()).toString());
        }
        return wb;
    }

    @Override
    public HSSFWorkbook exportRecord(List list, String uid) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("优惠券使用日志");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecord.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecord[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }
        Map temp;
        String tempStr;
        for (int i = 0; i < list.size(); i++) {
            temp = (Map) list.get(i);
            row = sheet.createRow(i + 1);
            tempStr = temp.get("num") == null ? "" : temp.get("num").toString();
            row.createCell(0).setCellValue(tempStr);
            tempStr = temp.get("gongdanId") == null ? "" : temp.get("gongdanId").toString();
            row.createCell(1).setCellValue(tempStr);

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            tempStr = temp.get("useDate") == null ? "" : temp.get("useDate").toString();
            try {
                tempStr = simpleDateFormat.format(Long.valueOf(tempStr));
            } catch (Exception e) {
                tempStr = "";
            }
            row.createCell(2).setCellValue(tempStr);

            tempStr = temp.get("useRemark") == null ? "" : temp.get("useRemark").toString();
            row.createCell(3).setCellValue(tempStr);

            tempStr = temp.get("printDate") == null ? "" : temp.get("printDate").toString();
            try {
                tempStr = simpleDateFormat.format(Long.valueOf(tempStr));
            } catch (Exception e) {
                tempStr = "";
            }
            row.createCell(4).setCellValue(tempStr);

            tempStr = temp.get("activityName") == null ? "" : temp.get("activityName").toString();
            row.createCell(5).setCellValue(tempStr);

            tempStr = temp.get("gift") == null ? "" : temp.get("gift").toString();
            row.createCell(6).setCellValue(tempStr);
            //活动成本= 材料成本+工时成本

            // "发券时间", "所属活动", "赠送礼品", "活动成本", "礼品价值", "车牌号", "车主姓名", "联系电话", "剩余金额", "使用金额"};

            String gcost = temp.get("gscost") == null ? "0" : temp.get("gscost").toString();
            String des = temp.get("degression") == null ? "0" : temp.get("degression").toString();
            try {
                tempStr = String.valueOf(new BigDecimal(gcost).add(new BigDecimal(des)).setScale(2, BigDecimal.ROUND_HALF_EVEN).doubleValue());

            } catch (Exception e) {
                tempStr = "0";
            }
            row.createCell(7).setCellValue(tempStr);

            // 礼品价值
            tempStr = temp.get("cost") == null ? "0" : temp.get("cost").toString();
            row.createCell(8).setCellValue(tempStr);

            tempStr = temp.get("plateNum") == null ? "" : temp.get("plateNum").toString();
            row.createCell(9).setCellValue(tempStr);

            tempStr = temp.get("contactName") == null ? "" : temp.get("contactName").toString();
            row.createCell(10).setCellValue(tempStr);

            tempStr = temp.get("contactPhone") == null ? "" : temp.get("contactPhone").toString();
            row.createCell(11).setCellValue(tempStr);

            tempStr = temp.get("remainder") == null ? "" : temp.get("remainder").toString();
            row.createCell(12).setCellValue(tempStr);

            tempStr = temp.get("useMoney") == null ? "" : temp.get("useMoney").toString();
            row.createCell(13).setCellValue(tempStr);


        }
        return wb;
    }

    /***
     * 分页查询
     * @param activityId
     * @param plateNum
     * @param startDate
     * @param endDate
     * @param uid
     * @param start
     * @param limit
     * @param companyId
     * @param area
     * @param brand
     * @return
     * @throws Exception
     */
    @Override
    public Map findListRecord(String activityId, String plateNum, String startDate, String endDate, String uid, String start,
                              String limit, String companyId, String area, String brand) throws Exception {
//        List params = new ArrayList();
//        String sql = "select * from t_crm_car_coupon_userecord where 1=1 and company_id = ?";
//        params.add(companyId);
        Map pageMap = this.useRecordPage(activityId, plateNum, startDate, endDate, uid, "0",
                "6553500", companyId, area, brand);
        return pageMap;
    }

    /***
     * 根据条件查询优惠券详细列表
     * @param id
     * @param type
     * @param timeType
     * @param startDate
     * @param stopDate
     * @return
     * @throws Exception
     */
    @Override
    public List CouponPage(String id, String type, String timeType, String startDate, String stopDate) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append("SELECT                                                   ");
        sqlBuffer.append("ta.gift,                                                 ");
        sqlBuffer.append("ta.degression,                                           ");
        sqlBuffer.append("tc.num,                                                  ");
        sqlBuffer.append("ta.start_date,                                           ");
        sqlBuffer.append("ta.stop_date,                                            ");
        sqlBuffer.append("tc.dayin_date,                                           ");
        sqlBuffer.append("tc.contact_name,                                         ");
        sqlBuffer.append("tc.contact_phone,                                        ");
        sqlBuffer.append("tc.plate_num,                                            ");
        sqlBuffer.append("CASE WHEN tco.order_num is null then '' else tco.order_num end  gongdanId,                                 ");
        sqlBuffer.append("tc.use_date ,                                            ");
        sqlBuffer.append("ta.name ,ta.degression+ta.gscost as activityCost ");
        sqlBuffer.append("FROM t_crm_car_coupon tc                                 ");
        sqlBuffer.append("LEFT JOIN t_crm_car_activity ta on tc.activity_id=ta.id  ");
        sqlBuffer.append("left join t_crm_car_workorder tco ON tco.order_num = tc.gongdan_id and tco.company_id=tc.company_id  ");
        sqlBuffer.append("WHERE  tc.status <> '-1' ");

        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择活动！");
        } else {
            sqlBuffer.append(" and tc.activity_id = ? ");
            params.add(id);
        }
        if (!type.isEmpty()) {
            if (type.equals("2")) {
                sqlBuffer.append(" and tc.status = ? ");
                params.add(type);
            } else {
                sqlBuffer.append(" and tc.status != '2' ");
            }
        }
        //根据时间类型判断条件
        if (!timeType.isEmpty() && timeType != null) {
            //   if (data != null && !data.isEmpty()) {
            switch (timeType) {
                //打印时间
                case "0": {
                    if (startDate != null && !startDate.equals("")) {
                        String begin = startDate.toString();
                        sqlBuffer.append(" and tc.dayin_date >= ? ");
                        params.add(begin);
                    }
                    if (stopDate != null && !stopDate.equals("")) {
                        String end = stopDate.toString();
                        sqlBuffer.append(" and tc.dayin_date <= ? ");
                        params.add(end);
                    }
                }
                break;
                //使用时间
                case "1": {
                    if (startDate != null && !startDate.equals("")) {
                        String begin = startDate.toString();
                        sqlBuffer.append(" and tc.use_date >= ? ");
                        params.add(begin);
                    }
                    if (stopDate != null && !stopDate.equals("")) {
                        String end = stopDate.toString();
                        sqlBuffer.append(" and tc.use_date <= ? ");
                        params.add(end);
                    }
                }
                break;
                default:
                    throw new Exception("请选择已有时间类型！");
            }
            // }
        }

        List list = this.itCrmCarCouponDao.list(sqlBuffer.toString(), params.toArray());
        System.out.println(sqlBuffer.toString());
        System.out.println(params.toArray().toString());
        System.out.println(list.size());
        return list;
    }

    String[] excelHeaderRecordout = {"优惠券号", "打印时间", "使用时间", "使用工单", "车牌号", "车主姓名", "联系电话", "所属活动", "赠送礼品", "活动开始", "活动结束", "活动成本"};


    @Override

    public HSSFWorkbook eCoupon(List list) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("优惠券使用");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {

            Map map = (Map) list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(String.valueOf(map.get("num")));
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");
            //打印时间
            String sDate = String.valueOf(map.get("dayinDate"));
            System.out.println("1---" + sDate);
            if (sDate.toLowerCase().equals("null") || sDate.length() == 0) {
                sDate = "";
            } else {
                Long startDate = Long.valueOf(sDate);
                sDate = simpleDateFormat.format(startDate);
            }
            row.createCell(1).setCellValue(sDate);


            //使用时间
            sDate = String.valueOf(map.get("useDate"));
            System.out.println("2---" + sDate);
            if (sDate.toLowerCase().equals("null") || sDate.length() == 0) {
                sDate = "";
            } else {
                Long startDate = Long.valueOf(sDate);
                sDate = simpleDateFormat.format(startDate);
            }
            row.createCell(2).setCellValue(sDate);

            row.createCell(3).setCellValue(String.valueOf(map.get("gongdanId")));
            row.createCell(4).setCellValue(String.valueOf(map.get("plateNum")));
            row.createCell(5).setCellValue(String.valueOf(map.get("contactName")));
            row.createCell(6).setCellValue(String.valueOf(map.get("contactPhone")));
            row.createCell(7).setCellValue(String.valueOf(map.get("name")));
            row.createCell(8).setCellValue(String.valueOf(map.get("gift")));

            sDate = String.valueOf(map.get("startDate"));
            System.out.println("3---" + sDate);
            if (sDate.toLowerCase().equals("null") || sDate.length() == 0) {

                sDate = "";
            } else {
                Long startDate = Long.valueOf(sDate);
                sDate = simpleDateFormat.format(startDate);
            }
            row.createCell(9).setCellValue(sDate);
            //结束时间
            sDate = String.valueOf(map.get("stopDate"));
            System.out.println("4---" + sDate);
            if (sDate.toLowerCase().equals("null") || sDate.length() == 0) {

                sDate = "";
            } else {
                Long startDate = Long.valueOf(sDate);
                sDate = simpleDateFormat.format(startDate);
            }
            row.createCell(10).setCellValue(sDate);
            row.createCell(11).setCellValue(String.valueOf(map.get("activityCost")));
        }
        System.out.println();
        return wb;
    }


    /**
     * 保险代理 为客户生成活动优惠券
     *
     * @param activityId
     * @param carId
     * @param nums
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> addInsuranceActivityCoupon(String activityId, String carId, Integer nums, String uid, String qId,String companyId) throws Exception {
        //获得车辆信息
        Map<String, Object> carMap = this.getCarInfos(carId, uid,companyId);
        List<TInsuranceOrderCoupon> list = new ArrayList<>();
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        /****
         * 根据活动ID取得对应的活动信息
         *
         * **/
        //活动对应的发券数量
        Integer couCount = 1;
        //根据活动id回写活动发起部门
        Map temp = this.itCrmCarCouponDao.info("select tba.id,tba.o_name,ta.cou_count from  t_crm_car_activity ta" +
                " left join t_bc_organ tba on tba.id=ta.start_department where " +
                " ta.id = ? ", activityId);
        for (int i = 0; i < nums; i++) {
            TInsuranceOrderCoupon data = new TInsuranceOrderCoupon();
            //保存，审核状态为0未提交
            if (temp.size() > 0) {
                if (temp.get("id") != "" && !StringUtils.isEmpty(temp.get("id"))) {
                    data.setDept_id(String.valueOf(temp.get("id")));
                }
                if (temp.get("oName") != "" && !StringUtils.isEmpty(temp.get("oName"))) {
                    data.setDeptName(String.valueOf(temp.get("oName")));
                }
//                //设置优惠券活动数量
//                if (temp.get("couCount") != null && com.mysql.jdbc.StringUtils.isStrictlyNumeric(temp.get("couCount").toString())) {
//                    couCount = Integer.valueOf(temp.get("couCount").toString());
//                }
            }
            data.setCreateDate(Calendar.getInstance().getTimeInMillis());
            data.setCreateTime(Calendar.getInstance().getTimeInMillis());
            data.setApprovalStatus("0");
            data.setCaozuoName(uid);
            data.setStatus("0");
            data.setNum(System.currentTimeMillis() + "" + (int) ((Math.random() * 9 + 1) * 100000));
            data.setStatus("0");
            data.setUsercarId(StringUtil.formatEmpty(carMap.get("id")));
            data.setActivityId(activityId);
            data.setVin(StringUtil.formatNull(carMap.get("vin")));
            data.setPlateNum(StringUtil.formatNull(carMap.get("plateNum")));
            data.setContactName(StringUtil.formatNull(carMap.get("contactName")));
            data.setContactPhone(StringUtil.formatEmpty(carMap.get("contactPhone")));
            data.setCaozuoName(uid);
            data.setQuotationId(qId);
            data.setUsercarId(carId);
            data.setCompanyId(TokenUtil.getCompanyId(uid));
            data.setCreateDate(System.currentTimeMillis());
            data.setCreateUser(uid);
            list.add(data);
        }
        this.itCrmCarCouponDao.saveOrUpdateList(list);
        return this.getInsuranceActivityCoupon(qId, carId,companyId,uid);
    }

    /**
     * 获得车辆对应报价单的优惠券信息
     *
     * @param qId
     * @param carId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> getInsuranceActivityCoupon(String qId, String carId,String companyId,String uid) throws Exception {
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        //查询详情
        StringBuffer sql = new StringBuffer();
        sql.append(" select coupon.*,activty.gift,activty.name from t_insurance_order_coupon coupon " +
                " LEFT JOIN " +
                " T_CRM_CAR_ACTIVITY activty " +
                " ON " +
                " activty.id=coupon.activity_id ");
        List<Object> params = new ArrayList<>();
        sql.append(" where quotation_id=?  ");
        params.add(qId);
        if(!"".equals(companyId)){
            sql.append(" and coupon.company_id=? ");
            params.add(companyId);
        }

        return this.itCrmCarCouponDao.list(sql.toString(), params.toArray());
    }

    /**
     * 获取保险对应的车辆信息
     *
     * @param carId
     * @param uid
     * @return
     * @throws Exception
     */
    private Map<String, Object> getCarInfos(String carId, String uid,String companyId) throws Exception {
        String sql = " select plate_num,vin,contact_name,contact_phone,company_id from t_insurance_car  where id=? and company_id=? " +
                " union ALL " +
                " select plate_num,vin,owner_name,owner_tel,company_id from t_crm_car where id=? and company_id=? ";
        if("".equals(companyId)){
            companyId= TokenUtil.getCompanyId(uid);
        }
        List<Map<String, Object>> mapList = this.itCrmCarCouponDao.list(sql, carId, companyId, carId, companyId);
        if (mapList.size() == 0) {
            throw new Exception("未查询到车辆信息！！");
        }
        return mapList.get(0);
    }

    /**
     * 删除优惠券信息
     *
     * @param id
     * @throws Exception
     */
    @Override
    public void delInsuranceActivityCoupon(String id) throws Exception {
        TInsuranceOrderCoupon carCoupon = this.itCrmCarCouponDao.getInfo(id);
        if (carCoupon != null) {
            String qSql = "select * from t_insurance_quotation where id=?";
            Map map = this.itCrmCarCouponDao.info(qSql, carCoupon.getQuotationId());
            if (map.size() > 0) {
                String status = StringUtil.formatEmpty(map.get("status"));
                if (status.equals("2")) {
                    throw new Exception("该保单已出单，已发放优惠券信息不能删除!!");
                }
            }
        }
        String sql = "delete from T_INSURANCE_ORDER_COUPON where id=?";
        this.itCrmCarCouponDao.executeSQL(sql, id);
    }

    /**
     * 审核优惠券，并新增审批流
     *
     * @param id
     * @throws Exception
     */
    @Override
    public void updateApprovalStatus(TCrmCarApprovalFlowDetail data, String id, String status, String uid) throws Exception {
        List params = new ArrayList();
        params.add(id);
        params.add(uid);
        Map temp = new HashMap();
        if (!id.isEmpty()) {
            switch (status) {
                //任何一个节点退回--》未提交(需增加不同意审批明细)
                case "0": {
                    //判断单据状态是否已被审批
                    if (!this.operationAuthority(id, uid)) {
                        throw new Exception("登陆者目前无操作权限！");
                    }
                    data.setBillType("T_CRM_CAR_COUPON");
                    data.setBillId(id);
                    this.itCrmCarApprovalFlowDetailService.save(data, uid);
                }
                break;
                //提交审核按钮即--》卡券业务审核中
                case "1":
                    //只能提交自己的单据,并且状态为未提交的
                    temp = this.itCrmCarCouponDao.info("select approval_status as count from t_crm_car_coupon where " +
                            "id = ? and create_user = ?", params.toArray());
                    if (temp.size() == 0) {
                        throw new Exception("不能提交他人单据！");
                    } else if (!String.valueOf(temp.get("count")).equals("0")) {
                        throw new Exception("单据已提交！");
                    }
                    //更新单据提交信息
                    Date d = new Date();
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    this.itCrmCarCouponDao.executeSQL("update T_CRM_CAR_COUPON set applicant = ?,submit_date=? where  " +
                            " id = ? ", uid, df.format(d), id);
                    break;
                //卡券业务审核通过--》卡券财务审核中
                case "2": {
                    if (!this.operationAuthority(id, uid)) {
                        throw new Exception("登陆者目前无操作权限！");
                    }
                    data.setBillType("T_CRM_CAR_COUPON");
                    data.setBillId(id);
                    this.itCrmCarApprovalFlowDetailService.save(data, uid);
                }
                break;
                //卡券财务审核通过--》审核通过
                case "3": {
                    if (!this.operationAuthority(id, uid)) {
                        throw new Exception("登陆者目前无操作权限！");
                    }
                    data.setBillType("T_CRM_CAR_COUPON");
                    data.setBillId(id);
                    //回写单据审批完成时间
                    Date date = new Date();
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    this.itCrmCarCouponDao.executeSQL("update T_CRM_CAR_COUPON set finish_date = ? where  " +
                            " id = ? ", format.format(date), id);
                    this.itCrmCarApprovalFlowDetailService.save(data, uid);
                }
                break;
            }
            //先检查是否同角色已经审批
            this.itCrmCarCouponDao.executeSQL("update T_CRM_CAR_COUPON set approval_status = ? where  " +
                    " id = ? ", status, id);
        } else {
            throw new Exception("请选择优惠券！");
        }
    }

    //获取当前单据操作权限
    private boolean operationAuthority(String id, String uid) throws Exception {
        //单据当前审核状态与用户角色是否匹配（审核状态为1时有serviceRoleId、2时有financeRoleId审核）
        List params = new ArrayList();
        params.add(id);
        params.add(uid);
        Map temp = this.itCrmCarCouponDao.info("select * from t_bc_role_user where r_id in (select case approval_status when '1' then '" +
                serviceRoleId + "' when '2' then '" + financeRoleId + "' else '' end rId " +
                " from t_crm_car_coupon where id = ?) and u_id = ? limit 1 ", params.toArray());
        if (temp.size() > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 审批优惠券分页
     *
     * @param data
     * @param start
     * @param limit
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map approvalPage(Map data, String start, String limit, String uid) throws Exception {

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select p.id,tbu.u_name ,a.name,p.dept_name,tbu1.u_name createUser,p.create_time,p.plate_num,p.youxiaoqi,a.gift,p.approval_status,")
                .append(" case p.approval_status when '0' then '未提交' when '1' then '卡券业务审核中'  ")
                .append(" when '2' then '卡券财务审核中' when '3' then '审核通过' end shbz,p.num,p.vin ")
                .append(" from " + tabName + " p ")
                .append(" left join  t_crm_car_activity a on p.activity_id = a.id")
                .append(" left join t_bc_user tbu1  on p.create_user = tbu1.id ")
                .append(" left join t_bc_user tbu  on p.applicant = tbu.id where p.company_id = ?")
                .append(" and approval_status<>'3' and approval_status in " +
                        "(SELECT case r_id when '" + serviceRoleId + "' then '1' when '" + financeRoleId +
                        "' then '2' else  ''  end  from t_bc_role_user where u_id = ? )");
        params.add(TokenUtil.getCompanyId(uid));
        params.add(uid);
        sql.append(" and  p.status>'-1' ");
        //调拨状态
        if (data.get("allocationStatus") != null && !data.get("allocationStatus").equals("")) {
            sql.append(" and p.allocation_status = ? ");
            params.add(data.get("allocationStatus"));
        }
        //申请日期起
        if (data.get("enterTimeStart") != null && !data.get("enterTimeStart").equals("")) {
            sql.append(" and left(p.submit_date,10) >= ? ");
            params.add(data.get("enterTimeStart"));
        }
        //申请日期止
        if (data.get("enterTimeStop") != null && !data.get("enterTimeStop").equals("")) {
            sql.append(" and left(p.submit_date,10) <= ? ");
            params.add(data.get("enterTimeStop"));
        }
        //发出仓库
        if (data.get("fromWarehouse") != null && !data.get("fromWarehouse").equals("")) {
            sql.append(" and p.from_warehouse = ? ");
            params.add(data.get("fromWarehouse"));
        }
        //接受仓库
        if (data.get("aimWarehouse") != null && !data.get("aimWarehouse").equals("")) {
            sql.append(" and p.aim_warehouse = ? ");
            params.add(data.get("aimWarehouse"));
        }
        return this.itCrmCarCouponDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 已审批优惠券分页
     *
     * @param data
     * @param start
     * @param limit
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map approvaledPage(Map data, String start, String limit, String uid) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        sqlBuf.append(" select tcc.*,tad.approvaler,case tad.approval_status when 0 then '不同意' when 1 then '同意' end approvalStatus, ")
                .append(" tad.approval_date,tad.approval_opinion,tbc.u_name, ")
                .append(" case tcc.approval_status when '0' then '未提交' when '1' then '卡券业务审核中'  ")
                .append(" when '2' then '卡券财务审核中' when '3' then '审核通过' end shbz,tbc1.u_name createUser,a.name,a.gift ")
                .append(" from t_crm_car_approvalflow_detail tad ")
                .append(" left join t_crm_car_coupon tcc on tad.bill_id = tcc.id ")
                .append(" left join t_crm_car_activity a on tcc.activity_id = a.id ")
                .append(" left join t_bc_user tbc1 on tcc.create_user = tbc1.id ")
                .append(" left join t_bc_user tbc on tad.approvaler = tbc.id where tad.company_id = ? and approvaler in ")
                .append(" (select u_id from t_bc_role_user where r_id in  ")
                .append(" (select case r_id when '" + serviceRoleId + "' then r_id  when '" + financeRoleId + "' then r_id else '' end rId from t_bc_role_user where u_id = ? ) )");
        params.add(TokenUtil.getCompanyId(uid));
        params.add(uid);
        //申请日期起
        if (data.get("enterTimeStart") != null && !data.get("enterTimeStart").equals("")) {
            sqlBuf.append(" and left(tad.approval_date,10) >= ? ");
            params.add(data.get("enterTimeStart"));
        }
        //申请日期止
        if (data.get("enterTimeStop") != null && !data.get("enterTimeStop").equals("")) {
            sqlBuf.append(" and left(tad.approval_date,10) <= ? ");
            params.add(data.get("enterTimeStop"));
        }
        return this.itCrmCarCouponDao.page(sqlBuf.toString(), params.toArray(), start, limit);
    }


    /**
     * 保单出单查询车辆优惠券
     * @param uid
     * @param companyId
     * @param plateNum
     * @param vin
     * @param activityId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> insuranceCouponPage(String uid,String companyId,
                                                  String plateNum,String vin,String activityId) throws Exception {
        List<Object> params=new ArrayList<>();
        StringBuffer sql=new StringBuffer();
        sql.append("select * from (select c.remainder, c.dayin_date ,c.id,a.name, a.gift,a.gscost,a.cost,a.degression, " +
                " c.contact_name, c.use_date,c.use_money, c.youxiaoqi, " +
                " c.contact_phone, c.plate_num, c.num,  c.status,c.o_name," +
                " c.dept_name ,c.create_time," +
                " case approval_status when '0' then '未提交' when '1' then '卡券业务审核中' when '2' then '卡券财务审核中' " +
                " when '3' then '审核通过' end shbz ,c.saler " +
                " from (select c.*,bc.o_name from t_crm_car_coupon c ");
        sql.append(",");
        sql.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, "", "", sql, params, "");
        sql.append(" ) bc ");
        sql.append(" where bc.id=c.company_id )c");
        sql.append(" left join  t_crm_car_activity a on c.activity_id = a.id " +
                " where c.status > '0' ");
        if(!"".equals(StringUtil.formatEmpty(companyId))){
            sql.append(" and c.activity_id=? ");
            params.add(activityId);
        }
        if(!"".equals(StringUtil.formatEmpty(vin))){
            sql.append(" and c.vin =? ");
            params.add(vin);
        }
        if(!"".equals(plateNum)){
            sql.append(" and c.plate_num like ? ");
            params.add("%"+StringUtil.formatEmpty(plateNum)+"%");
        }
        sql.append(" and not EXISTS(select * from t_insurance_order_coupon where num=c.num) ");
        sql.append(")temp");
        return this.itCrmCarCouponDao.list(sql.toString(),params.toArray());
    }

    /**
     * 保存报价单中的赠品信息到临时表中
     * @param uid
     * @param qId
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> insuranceSaveCoupon(String uid,String qId,String id) throws Exception {
        String sql="INSERT INTO t_insurance_order_coupon(id, create_time, create_user, update_time, update_user, " +
                " activity_id, caozuo_name, chanzhi, combo_id, company_id, contact_name, contact_phone, create_date, " +
                " dayin_date, dayin_name, gongdan_id, match_amount, match_order_num, num, plate_num, remart, status, " +
                " use_date, use_remark, usercar_id, vin, youxiaoqi, dept_id, approval_status, dept_name,quotation_id) " +
                " select id, create_time, create_user, update_time, update_user, activity_id, caozuo_name, chanzhi, " +
                " combo_id, company_id, contact_name, contact_phone, create_date, dayin_date, dayin_name, gongdan_id, " +
                " match_amount, match_order_num, num, plate_num, remart, status, use_date, use_remark, usercar_id, vin," +
                " youxiaoqi, dept_id, approval_status, dept_name,'"+qId+"' " +
                " from t_crm_car_coupon c where c.id='"+id+"' and " +
                " not EXISTS(select * from t_insurance_order_coupon where id=c.id);";
        this.itCrmCarCouponDao.executeSQL(sql);
        return this.getInsuranceActivityCoupon(qId, "", "", uid);
    }

    /**
     * 删除报价单针对的优惠券信息
     * @param uid
     * @param qId
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> insuranceDelCoupon(String uid, String qId, String id) throws Exception {
        List<String> params=new ArrayList<>();
        String sql="delete from  t_insurance_order_coupon where quotation_id=?";
        params.add(qId);
        if(!"".equals(id)){
            sql+=" and id=?  ";
            params.add(id);
        }
        this.itCrmCarCouponDao.executeSQL(sql,params.toArray());
        return this.getInsuranceActivityCoupon(qId, "", "", uid);
    }

}


